#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : yj_ch_first_demand_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-10-16 17:15 
@Desc    : 有家&城宿合并首次正式邮件数据报表需求
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils import date_util
from report_system.utils import enum_util
from report_system.utils import excel_util
from report_system.report_specify.service_specify import city_refund_rate_month


def sales_situation_df(st, et, own_type):
    """
    销售情况
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    sql = """select 
            
             COALESCE(whs.city_name,'全国')  城市,
             if({own_type} = 1,'有家','城宿') 公司,
            if('{st}'='{et}','{st}',concat('{st}','-->','{et}')) 日期,
            count(distinct if(date(whtn.online_time) between '{st}' and '{et}',whc.house_id,null)) 新增上线房源,
            count(distinct if(date(whtn.offline_time) between '{st}' and '{et}',whc.house_id,null)) 新增下线房源,
            count(distinct if(whc.online_stock = 1,whc.house_id,null)) 在线房源,
            count(distinct if(whc.online_stock = 1 and whc.rent_stock = 0,whc.house_id,null)) 屏蔽房源,
            concat(round(sum(if(whc.online_stock = 1 and whc.rent_stock = 1,1,0))/sum(if(whc.online_stock = 1,1,0))*100,2),"%") 可租率,
            concat(round(sum(whc.have_order) /sum(if(whc.online_stock = 1 and rent_stock = 1,1,0)) * 100,2),"%") 可售房源入住率,
            if(whs.city_name is null , 100,round(sum(whc.have_order) /sum(if(whc.online_stock = 1,1,0)) * 100,2)) 排序,
            concat(round(sum(whc.have_order) /sum(if(whc.online_stock = 1,1,0)) * 100,2),"%") 在线房源入住率,
            round(sum(whc.income+whc.compensate_income)/sum(have_order)/100,2) ADR,
            round(sum(whc.income+whc.compensate_income)/sum(if(whc.online_stock = 1,1,0))/100,2) RevPar
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_time_node whtn on whs.house_id = whtn.house_id
            left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
            where whs.own_type = {own_type}
            and whs.house_state >= 100
            and (whc.online_stock = 1 or whc.have_order = 1)
            and whc.dt between '{st}' and '{et}'
            group by whs.city_name
            with rollup
            ;
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def sales_situation_last_year_df(st, et, own_type):
    """
    去年同期销售情况
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    st = date_util.date_sub(st, days=0, years=1)
    et = date_util.date_sub(et, days=0, years=1)
    sql = """select 
            
            COALESCE(whs.city_name,'全国') 城市,
            if({own_type} = 1,'有家','城宿') 公司,
            concat(round(sum(whc.have_order) /sum(if(whc.online_stock = 1,1,0)) * 100,2),"%") 去年同期在线房源入住率,
            round(sum(whc.income+whc.compensate_income)/sum(have_order)/100,2) 去年同期ADR,
            round(sum(whc.income+whc.compensate_income)/sum(if(whc.online_stock = 1,1,0))/100,2) 去年同期RevPar
            
            from youjia_report.wh_house_static whs
            
            left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
            where whs.own_type = {own_type}
            and whc.dt between '{st}' and '{et}'
            group by whs.city_name
            with rollup;
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def ch_net_profit_df(st, et):
    """
    城宿净利
    :param st:
    :param et:
    :return:
    """
    sql = """select 
    coalesce(whs.city_name,"全国") 城市,
    '城宿' 公司,
    count(distinct whs.house_id) 房源量,
    round(ifnull(cchb.month_price/30*DATEDIFF('{et}',if(date(whtn.online_time) <'{st}','{st}',date(whtn.online_time)))/100,0),2) 租房支出,
    round(ifnull(poa.arrear_amount/100,0),2) 运营支出,
    round(sum(whc.income+whc.compensate_income)/100,2) 总收入,
    round(sum(whc.income+whc.compensate_income)/100 * cct.base_commission_amount /100,2) 佣金,
    cct.base_commission_amount 佣金比例
    from youjia_report.wh_house_static whs
    left join youjia_report.wh_house_time_node whtn on whtn.house_id = whs.house_id  
    left join youjia.lod_house_compact lhc on lhc.house_id = whs.house_id and lhc.is_delete = 0
    left join youjia.cs_compact_trust cct on cct.id = lhc.compact_trust_id and cct.is_delete = 0
    left join youjia.cs_compact_house_bill cchb on cchb.compact_house_id = cct.compact_house_id and cchb.is_delete = 0 and cchb.pay_cycle_start_date < '{et}'
    left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
    left join (select house_id,sum(arrear_amount) arrear_amount from youjia_pay.pay_owner_arrear where arrear_type >= 101 and is_delete = 0 and parent_id is null and date(create_time) between '{st}' and '{et}'    group by house_id) poa on poa.house_id = whs.house_id
    where whs.own_type = 2
    and whs.city_id <> 376
    and whs.house_state >= 100
    and (whc.online_stock = 1 or have_order = 1)
    and whc.dt between '{st}' and '{et}'
    group by whs.city_name
    with rollup;""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_youjia)


def yj_net_profit_df(st, et):
    """
    有家净利
    :param st:
    :param et:
    :return:
    """
    sql = """select 
            coalesce(whs.city_name,'全国')  城市,
            '有家' 公司,
            round((sum((income+compensate_income)*0.94*ifnull(ich.youjia_layer/10000,1)) - 
                    sum(rent_day))/100/count(distinct whs.house_id),2) 投资人套均净利,
            concat(round(sum(income+compensate_income)/sum(rent_day)*100,2),"%") 套均租金完成比
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whs.house_id = whc.house_id
            left join youjia.invest_compact_house ich on ich.house_id = whs.house_id
            where whs.house_state >= 100
            and (whc.online_stock = 1 or have_order = 1)
            and whc.dt between '{st}' and '{et}'
            group by whs.city_name
            with rollup
            ;""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_youjia)


def yj_ch_refund_rate(st, et):
    """
    城宿和有家的退款率
    :param st:
    :param et:
    :return:
    """
    ch_city_refund_rate_month_df = city_refund_rate_month(st, et, enum_util.CITY_HOME, False)
    ch_city_refund_rate_month_df = df_util.df_reset_index(ch_city_refund_rate_month_df)

    yj_city_refund_rate_month_df = city_refund_rate_month(st, et, enum_util.YOU_JIA, False)
    yj_city_refund_rate_month_df = df_util.df_reset_index(yj_city_refund_rate_month_df)

    city_refund_rate_month_df = pd.concat([ch_city_refund_rate_month_df, yj_city_refund_rate_month_df], axis=0)
    return city_refund_rate_month_df[['城市', '公司', '退款率']]


def ch_net_profit(st=None, et=None):
    st = st or date_util.cur_month_first()
    et = et or date_util.curdate()
    df = ch_net_profit_df(st, et)
    df["套均净利"] = df.apply(lambda row: round((row['总收入'] - row['租房支出']
                                             - row['运营支出'] - row['佣金']) / row['房源量'], 2), axis=1)
    df['套均运营成本'] = df.apply(lambda row: round(row['运营支出'] / row['房源量'], 2), axis=1)
    df = df[['城市', '公司', '套均净利', '套均运营成本']]

    st_last_year = date_util.date_sub(st, days=0, years=1)
    et_last_year = date_util.date_sub(et, days=0, years=1)
    df_last_year = ch_net_profit_df(st_last_year, et_last_year)
    df_last_year["去年套均净利"] = df_last_year.apply(lambda row: round((row['总收入'] - row['租房支出']
                                                                   - row['运营支出'] - row['佣金']) / row['房源量'], 2), axis=1)
    df_last_year['去年套均运营成本'] = df_last_year.apply(lambda row: round(row['运营支出'] / row['房源量'], 2), axis=1)
    df_last_year = df_last_year[['城市', '公司', '去年套均净利', '去年套均运营成本']]

    df = pd.merge(df, df_last_year, how='left')
    df = df[['城市', '公司', '套均净利', '去年套均净利', '套均运营成本', '去年套均运营成本']]
    return df


def yj_net_profit(st, et):
    """
    有家净利
    :param st:
    :param et:
    :return:
    """
    st = st or date_util.cur_month_first()
    et = et or date_util.curdate()
    df = yj_net_profit_df(st, et)

    st_last_year = date_util.date_sub(st, days=0, years=1)
    et_last_year = date_util.date_sub(et, days=0, years=1)
    df_last_year = yj_net_profit_df(st_last_year, et_last_year)
    df_last_year["去年投资人套均净利"] = df_last_year["投资人套均净利"]
    df_last_year['去年套均租金完成比'] = df_last_year["套均租金完成比"]
    df_last_year = df_last_year[['城市', '公司', "去年投资人套均净利", '去年套均租金完成比']]

    df = pd.merge(df, df_last_year, how='left')
    df = df[['城市', '公司', '投资人套均净利', '去年投资人套均净利', '套均租金完成比', '去年套均租金完成比']]
    return df


def sales_situation(st, et):
    ch_sales_situation_df = sales_situation_df(st, et, enum_util.CITY_HOME)
    ch_sales_situation_last_year_df = sales_situation_last_year_df(st, et, enum_util.CITY_HOME)
    ch_df = pd.merge(ch_sales_situation_df, ch_sales_situation_last_year_df, how='left')
    yj_sales_situation_df = sales_situation_df(st, et, enum_util.YOU_JIA)
    yj_sales_situation_last_year_df = sales_situation_last_year_df(st, et, enum_util.YOU_JIA)
    yj_df = pd.merge(yj_sales_situation_df, yj_sales_situation_last_year_df, how='left')
    df = pd.concat([yj_df, ch_df])
    df = df_util.df_sort(df, '排序')
    df = df_util.df_drop(df, '排序')
    city_refund_rate_month_df = yj_ch_refund_rate(st, et)

    df = pd.merge(df, city_refund_rate_month_df, how='left')

    return df


def city_sales_situation_yesterday(yesterday=None):
    """
    销售情况	昨日
    :param yesterday:
    :return:
    """
    yesterday = yesterday or date_util.curdate()
    return sales_situation(yesterday, yesterday)


def city_sales_situation_month(st=None, et=None):
    """
    销售情况	本月截止日
    :param st:
    :param et:
    :return:
    """
    st = st or date_util.cur_month_first()
    et = et or date_util.curdate()

    df = sales_situation(st, et)
    df = df_util.df_set_index(df, ['城市', '公司'])
    df = df_util.df_set_first_title(df, "销售情况")

    _yj_net_profit_df = yj_net_profit(st, et)
    _yj_net_profit_df = df_util.df_set_index(_yj_net_profit_df, ['城市', '公司'])
    _yj_net_profit_df = df_util.df_set_first_title(_yj_net_profit_df, "有家净利")

    df = pd.merge(df, _yj_net_profit_df, how='left', left_index=True, right_index=True)

    _ch_net_profit_df = ch_net_profit(st, et)
    _ch_net_profit_df = df_util.df_set_index(_ch_net_profit_df, ['城市', '公司'])
    _ch_net_profit_df = df_util.df_set_first_title(_ch_net_profit_df, "城宿净利")

    df = pd.merge(df, _ch_net_profit_df, how='left', left_index=True, right_index=True)

    df = df_util.df_reset_index(df)
    return df


if __name__ == '__main__':
    a = city_sales_situation_month()
    print(a)
    b = city_sales_situation_yesterday()
    excel_util.pd_to_excel({'a': a, "b": b}, "00a0a0a",merge_cells=True)
    # print(a)
